'''
Author: 13896370136 3361098@qq.com
Date: 2024-02-06 15:42:27
LastEditors: 13896370136 3361098@qq.com
LastEditTime: 2024-03-12 10:29:49
FilePath: \ShopExport\outexcel.py
Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
'''
import time
import xlwings as xw

def write_excel(data):
    fp = f'{time.strftime("%Y%m%d%H%M%S", time.localtime())}.xlsx'
    app = xw.App(visible=False, add_book=False)
    book = app.books.add()
    sheet3 = book.sheets[0]

    # wb = xw.Book()
    # sheet3 = wb.sheets[0]

    sheet3.range(1, 1).value = "商品名称"
    sheet3.range(1, 2).value = "系统分类"
    sheet3.range(1, 3).value = "成本价"
    sheet3.range(1, 4).value = "零售价"
    sheet3.range(1, 5).value = "库存数量"
    sheet3.range(1, 6).value = "条码"

    sheet3.range(1, 1).color = 255,200,255
    sheet3.range(1, 2).color = 255,200,255
    sheet3.range(1, 3).color = 255,200,255
    sheet3.range(1, 4).color = 255,200,255
    sheet3.range(1, 5).color = 255,200,255
    sheet3.range(1, 6).color = 255,200,255

    sheet3.range(1, 1).api.Font.Bold = True
    sheet3.range(1, 2).api.Font.Bold = True
    sheet3.range(1, 3).api.Font.Bold = True
    sheet3.range(1, 4).api.Font.Bold = True
    sheet3.range(1, 5).api.Font.Bold = True
    sheet3.range(1, 6).api.Font.Bold = True
    for i in range(len(data)):
        sheet3.range(i+2, 3).api.NumberFormat = "0.00"
        sheet3.range(i+2, 4).api.NumberFormat = "0.00"
        sheet3.range(i+2, 6).api.NumberFormat = "@"
        for k,v in data[i].items():
            col = 0
            if k == "name": col = 1
            if k == "tag": col = 2
            if k == "cprice": col = 3
            if k == "sprice": col = 4
            if k == "stock": col = 5
            if k == "code": col = 6
            sheet3.range(i+2, col).value = v

    # wb.save(fp)  #保存修改
    # wb.close()  #关闭wb

    book.save(fp)
    book.close()
    app.quit()
    return fp